import re
from PyQt5.QtCore import QThread, pyqtSignal

from control.Databasemanage import DatabaseManager

db_manager = DatabaseManager()
db_manager.connect()
cursor = db_manager.connection.cursor()

class SelectThread(QThread):
    """用于处理查询数据的线程"""
    data_signal = pyqtSignal(list, int, int)  # 信号传递数据和总页数
    data2_signal = pyqtSignal(list, int, int)

    def __init__(self, subject, keyword, split_answer_status, page_number, items_per_page, mainwindows, parent=None):
        super().__init__(parent)
        self.subject = subject
        self.keyword = keyword
        self.split_answer_status = split_answer_status
        self.page_number = page_number
        self.items_per_page = items_per_page
        self.mainwindows = mainwindows
        self.cn2en = {
            "序号": "tId",
            "科目代码": "subject_code",
            "科目名称": "subject_name",
            "考题": "answer_qr_code",
            "考生号": "student_id",
            "姓名": "student_name",
            "小题数": "child_num",
            "阅卷状态": "split_answer_status",
            "总分": "answer_score",
            "得分": "total_score",
            "复阅状态": "STATE",
            "阅卷时间": "modify_time",
            "操作": "operation",
            "详情": "details"
        }
        self.cn2en1 = {
            "序号": "tId",
            "科目代码": "subject_code",
            "科目名称": "subject_name",
            "考题": "answer_qr_code",
            "考生号": "student_id",
            "姓名": "student_name",
            "小题号": "answer_no",
            "参考答案": "answer_standard",
            "识别值": "answer_student",
            "评价结果": "evaluate_results",
            "满分": "answer_score",
            "得分": "get_score",
            "复阅状态": "STATE",
            "阅卷时间": "modify_time"
        }
    def run(self):
        """线程运行时执行的函数"""
        subject = self.subject
        keyword = self.keyword
        split_answer_status = self.split_answer_status
        page_number = self.page_number
        items_per_page = self.items_per_page

        if self.mainwindows.pushButton_25.text() == "查看详分":
            if subject != "请选择科目":
                # 这里的正则表达式匹配最外层的括号内的内容
                match = re.search(r'\(([^)]+)\)', subject)
                subject_code = match.group(1)
                if self.mainwindows.comboBox_3.currentText() == "选择阅卷答题":
                    if len(keyword) != 0:
                        # 科目 + 关键词 + 阅卷状态为空
                        if split_answer_status == "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND student_id = ?",
                                ('1%', '2%', '3%', subject_code, keyword))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""
                        #科目 + 关键字 + 阅卷状态不为空
                        elif split_answer_status != "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND student_id = ? AND split_answer_status = ?",
                                ('1%', '2%', '3%', subject_code, keyword, split_answer_status))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""

                        # 准备字典来存储 subject_code 和 subject_name 的映射关系
                        cursor.execute(
                            "SELECT subject_code, subject_name FROM exam_subject WHERE subject_code = ?",
                            (subject_code))
                        subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute("SELECT student_id, student_name FROM student_info WHERE student_id = ?",
                                       (keyword))
                        student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT answer_id, child_num, answer_score FROM exam_answer_into WHERE child_num != 0")
                        question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE student_id = ? and subject_code = ?",
                            (keyword, subject_code))
                        studentscore_dic = cursor.fetchall()
                        scores_dict = self.calculate_scores(studentscore_dic)
                        # 字段名列表
                        field_names = [
                            "paper_id", "subject_code", "student_id", "answer_id", "answer_qr_code",
                            "split_answer_status", "modify_time", "subject_name", "student_name",
                            "child_num", "answer_score"
                        ]

                        # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                        new_rows = []
                        for row in rows:
                            subject_code = row[1]
                            subject_name = subject_dict.get(subject_code, "Null")
                            new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                            if new_row[5] == 0:
                                new_row[5] = "未阅"
                            elif new_row[5] == 1:
                                new_row[5] = "已完成"
                            elif new_row[5] == 2:
                                new_row[5] = "已阅失败"
                            new_rows.append(new_row)
                        self.now_data = []
                        # 再次循环 new_rows 来添加 student_name 和 answer_score
                        for new_row in new_rows:
                            student_id = new_row[2]
                            student_name = student_dic.get(student_id, "Null")
                            answer_id = new_row[3]
                            child_num, answer_score = question_dic.get(answer_id, ("Null", "Null"))
                            student_row = new_row + [student_name, child_num,
                                                     answer_score]  # 将 student_name 和 answer_score 添加到 new_row

                            # 提取第一位和第二位
                            question_type = student_row[4][0]  # 第一位代表题目类型
                            section_number = student_row[4][1]  # 第二位代表大题号
                            if question_type == "1":
                                question_type = "单选题"
                            elif question_type == "2":
                                question_type = "多选题"
                            elif question_type == "3":
                                question_type = "判断题"

                            # 转换第二位为中文大写数字
                            converted_section_number = self.convert_section_number(section_number)

                            # 组合新的值
                            new_value = f"{converted_section_number}、{question_type}"
                            student_row[4] = new_value  # 更新 new_row[3] 的值

                            # 创建一个字典来存储字段名和对应的值
                            row_dict = dict(zip(field_names, student_row))

                            self.now_data.append(row_dict)

                        # 创建一个字典，以paper_id, subject_code, student_id, answer_id为键，total_score为值
                        total_score_dict = {
                            (ts['paper_id'], ts['subject_code'], ts['student_id'], ts['answer_id']): ts[
                                'total_score']
                            for ts in scores_dict}

                        # 遍历students_info，如果键存在，则添加total_score
                        for student in self.now_data:
                            key = (
                                student['paper_id'], student['subject_code'], student['student_id'],
                                student['answer_id'])
                            if key in total_score_dict:
                                student['total_score'] = total_score_dict[key]
                            else:
                                student['total_score'] = 0

                        self.results_data = []
                        for index, entry in enumerate(self.now_data, start=1):
                            row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                            # 使用 self.cn2en 提取相应的值
                            for key in list(self.cn2en.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                                row.append(str(entry.get(str(self.cn2en[key]), " ")))  # 以字符串显示
                            self.results_data.append(row)
                        total_count = len(self.now_data)
                        total_pages = (total_count + items_per_page - 1) // items_per_page
                        self.data_signal.emit(self.results_data, total_pages, total_count)

                    elif len(keyword) == 0:
                        # 科目 + 无关键字 + 无阅卷状态
                        if split_answer_status == "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ?",
                                ('1%', '2%', '3%', subject_code))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""
                        # 科目 + 无关键字 + 阅卷状态
                        elif split_answer_status != "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND split_answer_status = ?",
                                ('1%', '2%', '3%', subject_code, split_answer_status))
                            rows = cursor.fetchall()

                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""

                        # 准备字典来存储 subject_code 和 subject_name 的映射关系
                        cursor.execute(
                            "SELECT subject_code, subject_name FROM exam_subject WHERE subject_code = ?",
                            (subject_code))
                        subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute("SELECT student_id, student_name FROM student_info")
                        student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT answer_id, child_num, answer_score FROM exam_answer_into WHERE child_num != 0")
                        question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE subject_code = ?",
                            (subject_code))
                        studentscore_dic = cursor.fetchall()
                        scores_dict = self.calculate_scores(studentscore_dic)
                        # 字段名列表
                        field_names = [
                            "paper_id", "subject_code", "student_id", "answer_id", "answer_qr_code",
                            "split_answer_status", "modify_time", "subject_name", "student_name",
                            "child_num", "answer_score"
                        ]

                        # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                        new_rows = []
                        for row in rows:
                            subject_code = row[1]
                            subject_name = subject_dict.get(subject_code, "Null")
                            new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                            if new_row[5] == 0:
                                new_row[5] = "未阅"
                            elif new_row[5] == 1:
                                new_row[5] = "已完成"
                            elif new_row[5] == 2:
                                new_row[5] = "已阅失败"
                            new_rows.append(new_row)
                        self.now_data = []
                        # 再次循环 new_rows 来添加 student_name 和 answer_score
                        for new_row in new_rows:
                            student_id = new_row[2]
                            student_name = student_dic.get(student_id, "Null")
                            answer_id = new_row[3]
                            child_num, answer_score = question_dic.get(answer_id, ("Null", "Null"))
                            student_row = new_row + [student_name, child_num,
                                                     answer_score]  # 将 student_name 和 answer_score 添加到 new_row

                            # 提取第一位和第二位
                            question_type = student_row[4][0]  # 第一位代表题目类型
                            section_number = student_row[4][1]  # 第二位代表大题号
                            if question_type == "1":
                                question_type = "单选题"
                            elif question_type == "2":
                                question_type = "多选题"
                            elif question_type == "3":
                                question_type = "判断题"

                            # 转换第二位为中文大写数字
                            converted_section_number = self.convert_section_number(section_number)

                            # 组合新的值
                            new_value = f"{converted_section_number}、{question_type}"
                            student_row[4] = new_value  # 更新 new_row[3] 的值

                            # 创建一个字典来存储字段名和对应的值
                            row_dict = dict(zip(field_names, student_row))

                            self.now_data.append(row_dict)

                        # 创建一个字典，以paper_id, subject_code, student_id, answer_id为键，total_score为值
                        total_score_dict = {
                            (ts['paper_id'], ts['subject_code'], ts['student_id'], ts['answer_id']): ts[
                                'total_score']
                            for ts in scores_dict}

                        # 遍历students_info，如果键存在，则添加total_score
                        for student in self.now_data:
                            key = (
                                student['paper_id'], student['subject_code'], student['student_id'],
                                student['answer_id'])
                            if key in total_score_dict:
                                student['total_score'] = total_score_dict[key]
                            else:
                                student['total_score'] = 0

                        self.results_data = []
                        for index, entry in enumerate(self.now_data, start=1):
                            row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                            # 使用 self.cn2en 提取相应的值
                            for key in list(self.cn2en.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                                row.append(str(entry.get(str(self.cn2en[key]), " ")))  # 以字符串显示
                            self.results_data.append(row)

                        total_count = len(self.now_data)
                        total_pages = (total_count + items_per_page - 1) // items_per_page
                        self.data_signal.emit(self.results_data, total_pages, total_count)

                elif self.mainwindows.comboBox_3.currentText() != "选择阅卷答题":
                    # 提取第一位和第二位
                    question_type = self.mainwindows.comboBox_3.currentText().split("、")[1]  # 第一位代表题目类型
                    section_number = self.mainwindows.comboBox_3.currentText().split("、")[0]  # 第二位代表大题号
                    if question_type == "单选题":
                        question_type = "1"
                    elif question_type == "多选题":
                        question_type = "2"
                    elif question_type == "判断题":
                        question_type = "3"

                    # 转换第二位为中文大写数字
                    converted_section_number = self.convert_chinese_to_arabic(section_number)
                    # 组合新的值
                    new_value = f"{question_type}{converted_section_number}"
                    if len(keyword) == 0:
                        if split_answer_status != "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND split_answer_status = ? AND answer_qr_code = ?",
                                ('1%', '2%', '3%', subject_code, split_answer_status, new_value))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""
                        elif split_answer_status == "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND answer_qr_code = ?",
                                ('1%', '2%', '3%', subject_code, new_value))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""

                        # 准备字典来存储 subject_code 和 subject_name 的映射关系
                        cursor.execute(
                            "SELECT subject_code, subject_name FROM exam_subject WHERE subject_code = ?",
                            (subject_code))
                        subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute("SELECT student_id, student_name FROM student_info")
                        student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT answer_id, child_num, answer_score FROM exam_answer_into WHERE child_num != 0")
                        question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE subject_code = ?",
                            (subject_code))
                        studentscore_dic = cursor.fetchall()
                        scores_dict = self.calculate_scores(studentscore_dic)

                        # 字段名列表
                        field_names = [
                            "paper_id", "subject_code", "student_id", "answer_id", "answer_qr_code",
                            "split_answer_status", "modify_time", "subject_name", "student_name",
                            "child_num", "answer_score"
                        ]

                        # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                        new_rows = []
                        for row in rows:
                            subject_code = row[1]
                            subject_name = subject_dict.get(subject_code, "Null")
                            new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                            if new_row[5] == 0:
                                new_row[5] = "未阅"
                            elif new_row[5] == 1:
                                new_row[5] = "已完成"
                            elif new_row[5] == 2:
                                new_row[5] = "已阅失败"
                            new_rows.append(new_row)
                        self.now_data = []
                        # 再次循环 new_rows 来添加 student_name 和 answer_score
                        for new_row in new_rows:
                            student_id = new_row[2]
                            student_name = student_dic.get(student_id, "Null")
                            answer_id = new_row[3]
                            child_num, answer_score = question_dic.get(answer_id, ("Null", "Null"))
                            student_row = new_row + [student_name, child_num,
                                                     answer_score]  # 将 student_name 和 answer_score 添加到 new_row

                            # 提取第一位和第二位
                            question_type = student_row[4][0]  # 第一位代表题目类型
                            section_number = student_row[4][1]  # 第二位代表大题号
                            if question_type == "1":
                                question_type = "单选题"
                            elif question_type == "2":
                                question_type = "多选题"
                            elif question_type == "3":
                                question_type = "判断题"

                            # 转换第二位为中文大写数字
                            converted_section_number = self.convert_section_number(section_number)

                            # 组合新的值
                            new_value = f"{converted_section_number}、{question_type}"
                            student_row[4] = new_value  # 更新 new_row[3] 的值

                            # 创建一个字典来存储字段名和对应的值
                            row_dict = dict(zip(field_names, student_row))

                            self.now_data.append(row_dict)

                        # 创建一个字典，以paper_id, subject_code, student_id, answer_id为键，total_score为值
                        total_score_dict = {
                            (ts['paper_id'], ts['subject_code'], ts['student_id'], ts['answer_id']): ts[
                                'total_score']
                            for ts in scores_dict}

                        # 遍历students_info，如果键存在，则添加total_score
                        for student in self.now_data:
                            key = (
                                student['paper_id'], student['subject_code'], student['student_id'],
                                student['answer_id'])
                            if key in total_score_dict:
                                student['total_score'] = total_score_dict[key]
                            else:
                                student['total_score'] = 0

                        self.results_data = []
                        for index, entry in enumerate(self.now_data, start=1):
                            row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                            # 使用 self.cn2en 提取相应的值
                            for key in list(self.cn2en.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                                row.append(str(entry.get(str(self.cn2en[key]), " ")))  # 以字符串显示
                            self.results_data.append(row)
                        total_count = len(self.now_data)
                        total_pages = (total_count + items_per_page - 1) // items_per_page
                        self.data_signal.emit(self.results_data, total_pages, total_count)
                    elif len(keyword) != 0:
                        if split_answer_status == "":
                            # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND student_id = ? AND answer_qr_code = ?",
                                ('1%', '2%', '3%', subject_code, keyword, new_value))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""
                        elif split_answer_status != "":
                            cursor.execute(
                                "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND subject_code = ? AND student_id = ? AND answer_qr_code = ? AND split_answer_status = ?",
                                ('1%', '2%', '3%', subject_code, keyword, new_value, split_answer_status))
                            rows = cursor.fetchall()
                            for row in rows:
                                if row[6] is not None:
                                    row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                                elif row[6] is None:
                                    row[6] = ""
                        # 准备字典来存储 subject_code 和 subject_name 的映射关系
                        cursor.execute(
                            "SELECT subject_code, subject_name FROM exam_subject WHERE subject_code = ?",
                            (subject_code))
                        subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT student_id, student_name FROM student_info WHERE student_id = ?",
                            (keyword))
                        student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT answer_id, child_num, answer_score FROM exam_answer_into WHERE child_num != 0")
                        question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                        cursor.execute(
                            "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE student_id = ? and subject_code = ?",
                            (keyword, subject_code))
                        studentscore_dic = cursor.fetchall()
                        scores_dict = self.calculate_scores(studentscore_dic)

                        # 字段名列表
                        field_names = [
                            "paper_id", "subject_code", "student_id", "answer_id", "answer_qr_code",
                            "split_answer_status", "modify_time", "subject_name", "student_name",
                            "child_num", "answer_score"
                        ]

                        # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                        new_rows = []
                        for row in rows:
                            subject_code = row[1]
                            subject_name = subject_dict.get(subject_code, "Null")
                            new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                            if new_row[5] == 0:
                                new_row[5] = "未阅"
                            elif new_row[5] == 1:
                                new_row[5] = "已完成"
                            elif new_row[5] == 2:
                                new_row[5] = "已阅失败"
                            new_rows.append(new_row)
                        self.now_data = []
                        # 再次循环 new_rows 来添加 student_name 和 answer_score
                        for new_row in new_rows:
                            student_id = new_row[2]
                            student_name = student_dic.get(student_id, "Null")
                            answer_id = new_row[3]
                            child_num, answer_score = question_dic.get(answer_id, ("Null", "Null"))
                            student_row = new_row + [student_name, child_num,
                                                     answer_score]  # 将 student_name 和 answer_score 添加到 new_row

                            # 提取第一位和第二位
                            question_type = student_row[4][0]  # 第一位代表题目类型
                            section_number = student_row[4][1]  # 第二位代表大题号
                            if question_type == "1":
                                question_type = "单选题"
                            elif question_type == "2":
                                question_type = "多选题"
                            elif question_type == "3":
                                question_type = "判断题"

                            # 转换第二位为中文大写数字
                            converted_section_number = self.convert_section_number(section_number)

                            # 组合新的值
                            new_value = f"{converted_section_number}、{question_type}"
                            student_row[4] = new_value  # 更新 new_row[3] 的值

                            # 创建一个字典来存储字段名和对应的值
                            row_dict = dict(zip(field_names, student_row))

                            self.now_data.append(row_dict)

                        # 创建一个字典，以paper_id, subject_code, student_id, answer_id为键，total_score为值
                        total_score_dict = {
                            (ts['paper_id'], ts['subject_code'], ts['student_id'], ts['answer_id']): ts[
                                'total_score']
                            for ts in scores_dict}

                        # 遍历students_info，如果键存在，则添加total_score
                        for student in self.now_data:
                            key = (
                                student['paper_id'], student['subject_code'], student['student_id'],
                                student['answer_id'])
                            if key in total_score_dict:
                                student['total_score'] = total_score_dict[key]
                            else:
                                student['total_score'] = 0

                        self.results_data = []
                        for index, entry in enumerate(self.now_data, start=1):
                            row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                            # 使用 self.cn2en 提取相应的值
                            for key in list(self.cn2en.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                                row.append(str(entry.get(str(self.cn2en[key]), " ")))  # 以字符串显示
                            self.results_data.append(row)
                        total_count = len(self.now_data)
                        total_pages = (total_count + items_per_page - 1) // items_per_page
                        self.data_signal.emit(self.results_data, total_pages, total_count)
            elif subject == "请选择科目":
                if len(keyword) != 0:
                    if split_answer_status == "":
                        # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                        cursor.execute(
                            "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND student_id = ?",
                            ('1%', '2%', '3%', keyword))
                        rows = cursor.fetchall()
                        for row in rows:
                            if row[6] is not None:
                                row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                            elif row[6] is None:
                                row[6] = ""
                    elif split_answer_status != "":
                        # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                        cursor.execute(
                            "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND student_id = ? AND split_answer_status = ?",
                            ('1%', '2%', '3%', keyword, split_answer_status))
                        rows = cursor.fetchall()
                        for row in rows:
                            if row[6] is not None:
                                row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                            elif row[6] is None:
                                row[6] = ""
                    # 准备字典来存储 subject_code 和 subject_name 的映射关系
                    cursor.execute("SELECT subject_code, subject_name FROM exam_subject")
                    subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                    cursor.execute("SELECT student_id, student_name FROM student_info WHERE student_id = ?",
                                   (keyword))
                    student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                    cursor.execute(
                        "SELECT answer_id, child_num, answer_score FROM exam_answer_into WHERE child_num != 0")
                    question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                    cursor.execute(
                        "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE student_id = ?",
                        (keyword))
                    studentscore_dic = cursor.fetchall()
                    scores_dict = self.calculate_scores(studentscore_dic)

                    # 字段名列表
                    field_names = [
                        "paper_id", "subject_code", "student_id", "answer_id", "answer_qr_code",
                        "split_answer_status", "modify_time", "subject_name", "student_name",
                        "child_num", "answer_score"
                    ]

                    # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                    new_rows = []
                    for row in rows:
                        subject_code = row[1]
                        subject_name = subject_dict.get(subject_code, "Null")
                        new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                        if new_row[5] == 0:
                            new_row[5] = "未阅"
                        elif new_row[5] == 1:
                            new_row[5] = "已完成"
                        elif new_row[5] == 2:
                            new_row[5] = "已阅失败"
                        new_rows.append(new_row)
                    self.now_data = []
                    # 再次循环 new_rows 来添加 student_name 和 answer_score
                    for new_row in new_rows:
                        student_id = new_row[2]
                        student_name = student_dic.get(student_id, "Null")
                        answer_id = new_row[3]
                        child_num, answer_score = question_dic.get(answer_id, ("Null", "Null"))
                        student_row = new_row + [student_name, child_num,
                                                 answer_score]  # 将 student_name 和 answer_score 添加到 new_row

                        # 提取第一位和第二位
                        question_type = student_row[4][0]  # 第一位代表题目类型
                        section_number = student_row[4][1]  # 第二位代表大题号
                        if question_type == "1":
                            question_type = "单选题"
                        elif question_type == "2":
                            question_type = "多选题"
                        elif question_type == "3":
                            question_type = "判断题"

                        # 转换第二位为中文大写数字
                        converted_section_number = self.convert_section_number(section_number)

                        # 组合新的值
                        new_value = f"{converted_section_number}、{question_type}"
                        student_row[4] = new_value  # 更新 new_row[3] 的值

                        # 创建一个字典来存储字段名和对应的值
                        row_dict = dict(zip(field_names, student_row))

                        self.now_data.append(row_dict)

                    # 创建一个字典，以paper_id, subject_code, student_id, answer_id为键，total_score为值
                    total_score_dict = {
                        (ts['paper_id'], ts['subject_code'], ts['student_id'], ts['answer_id']): ts[
                            'total_score']
                        for ts in scores_dict}

                    # 遍历students_info，如果键存在，则添加total_score
                    for student in self.now_data:
                        key = (
                            student['paper_id'], student['subject_code'], student['student_id'],
                            student['answer_id'])
                        if key in total_score_dict:
                            student['total_score'] = total_score_dict[key]
                        else:
                            student['total_score'] = 0

                    self.results_data = []
                    for index, entry in enumerate(self.now_data, start=1):
                        row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                        # 使用 self.cn2en 提取相应的值
                        for key in list(self.cn2en.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                            row.append(str(entry.get(str(self.cn2en[key]), " ")))  # 以字符串显示
                        self.results_data.append(row)
                    total_count = len(self.now_data)
                    total_pages = (total_count + items_per_page - 1) // items_per_page
                    self.data_signal.emit(self.results_data, total_pages, total_count)
                elif len(keyword) == 0:
                    if split_answer_status == "":
                        cursor.execute(
                            "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?)",
                            ('1%', '2%', '3%'))
                        rows = cursor.fetchall()
                        for row in rows:
                            if row[6] is not None:
                                row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                            elif row[6] is None:
                                row[6] = ""
                    elif split_answer_status != "":
                        cursor.execute(
                            "SELECT paper_id, subject_code, student_id, answer_id, answer_qr_code, split_answer_status, modify_time FROM exam_scan_split WHERE (answer_qr_code LIKE ? OR answer_qr_code LIKE ? OR answer_qr_code LIKE ?) AND split_answer_status = ?",
                            ('1%', '2%', '3%', split_answer_status))
                        rows = cursor.fetchall()
                        for row in rows:
                            if row[6] is not None:
                                row[6] = row[6].strftime('%Y-%m-%d %H:%M:%S')
                            elif row[6] is None:
                                row[6] = ""

                    # 准备字典来存储 subject_code 和 subject_name 的映射关系
                    cursor.execute("SELECT subject_code, subject_name FROM exam_subject")
                    subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                    cursor.execute("SELECT student_id, student_name FROM student_info")
                    student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                    cursor.execute("SELECT answer_id, child_num, answer_score FROM exam_answer_into WHERE child_num != 0")
                    question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                    cursor.execute("SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer")
                    studentscore_dic = cursor.fetchall()
                    scores_dict = self.calculate_scores(studentscore_dic)

                    # 字段名列表
                    field_names = [
                        "paper_id", "subject_code", "student_id", "answer_id", "answer_qr_code",
                        "split_answer_status", "modify_time", "subject_name", "student_name",
                        "child_num", "answer_score"
                    ]

                    # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                    new_rows = []
                    for row in rows:
                        subject_code = row[1]
                        subject_name = subject_dict.get(subject_code, "Null")
                        new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                        if new_row[5] == 0:
                            new_row[5] = "未阅"
                        elif new_row[5] == 1:
                            new_row[5] = "已完成"
                        elif new_row[5] == 2:
                            new_row[5] = "已阅失败"
                        new_rows.append(new_row)
                    self.now_data = []
                    # 再次循环 new_rows 来添加 student_name 和 answer_score
                    for new_row in new_rows:
                        student_id = new_row[2]
                        student_name = student_dic.get(student_id, "Null")
                        answer_id = new_row[3]
                        child_num, answer_score = question_dic.get(answer_id, ("Null", "Null"))
                        student_row = new_row + [student_name, child_num,
                                                 answer_score]  # 将 student_name 和 answer_score 添加到 new_row

                        # 提取第一位和第二位
                        question_type = student_row[4][0]  # 第一位代表题目类型
                        section_number = student_row[4][1]  # 第二位代表大题号
                        if question_type == "1":
                            question_type = "单选题"
                        elif question_type == "2":
                            question_type = "多选题"
                        elif question_type == "3":
                            question_type = "判断题"

                        # 转换第二位为中文大写数字
                        converted_section_number = self.convert_section_number(section_number)

                        # 组合新的值
                        new_value = f"{converted_section_number}、{question_type}"
                        student_row[4] = new_value  # 更新 new_row[3] 的值

                        # 创建一个字典来存储字段名和对应的值
                        row_dict = dict(zip(field_names, student_row))

                        self.now_data.append(row_dict)

                    # 创建一个字典，以paper_id, subject_code, student_id, answer_id为键，total_score为值
                    total_score_dict = {
                        (ts['paper_id'], ts['subject_code'], ts['student_id'], ts['answer_id']): ts[
                            'total_score']
                        for ts in scores_dict}

                    # 遍历students_info，如果键存在，则添加total_score
                    for student in self.now_data:
                        key = (
                            student['paper_id'], student['subject_code'], student['student_id'],
                            student['answer_id'])
                        if key in total_score_dict:
                            student['total_score'] = total_score_dict[key]
                        else:
                            student['total_score'] = 0

                    self.results_data = []
                    for index, entry in enumerate(self.now_data, start=1):
                        row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                        # 使用 self.cn2en 提取相应的值
                        for key in list(self.cn2en.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                            row.append(str(entry.get(str(self.cn2en[key]), " ")))  # 以字符串显示
                        self.results_data.append(row)
                    total_count = len(self.now_data)
                    total_pages = (total_count + items_per_page - 1) // items_per_page
                    self.data_signal.emit(self.results_data, total_pages, total_count)

        elif self.mainwindows.pushButton_25.text() == "返回":
            pass

        elif len(keyword) != 0 and self.mainwindows.pushButton_25.text() == "返回":
            if subject != "请选择科目":
                # 使用正则表达式匹配括号内的内容
                # 这里的正则表达式匹配最外层的括号内的内容
                match = re.search(r'\(([^)]+)\)', subject)
                subject_code = match.group(1)
                # 准备字典来存储 subject_code 和 subject_name 的映射关系
                cursor.execute("SELECT subject_code, subject_name FROM exam_subject WHERE subject_code = ?", (subject_code))
                subject_dict = {row[0]: row[1] for row in cursor.fetchall()}

                cursor.execute("SELECT student_id, student_name FROM student_info WHERE student_id = ?", (keyword))
                student_dic = {row[0]: row[1] for row in cursor.fetchall()}

                cursor.execute("SELECT answer_id, answer_no, answer_qr_code FROM exam_answer_into")
                small_question_dic = {row[0]: (row[1], row[2]) for row in cursor.fetchall()}

                cursor.execute(
                    "SELECT paper_id, subject_code, student_id, answer_id, answer_standard, answer_student, answer_score, modify_time FROM exam_scan_split_answer WHERE subject_code = ? and student_id = ?",(subject_code, keyword))
                rows = cursor.fetchall()
                for row in rows:
                    if row[7] is not None:
                        row[7] = row[7].strftime('%Y-%m-%d %H:%M:%S')
                    elif row[7] is None:
                        row[7] = " "

                # 字段名列表
                field_names = [
                    "paper_id", "subject_code", "student_id", "answer_id", "answer_standard", "answer_student",
                    "answer_score", "modify_time",
                    "subject_name", "student_name", "answer_no", "answer_qr_code", "evaluate_results", "get_score"
                ]

                # 步骤 2: 使用每个 row 的 subject_code 去第二个表里查对应的 subject_name 并添加到每个 row 里
                new_rows = []
                for row in rows:
                    subject_code = row[1]
                    subject_name = subject_dict.get(subject_code, "Null")
                    new_row = list(row) + [subject_name]  # 将 subject_name 添加到 row
                    if new_row[5] == 0:
                        new_row[5] = "未阅"
                    elif new_row[5] == 1:
                        new_row[5] = "已完成"
                    elif new_row[5] == 2:
                        new_row[5] = "已阅失败"
                    new_rows.append(new_row)

                self.detail_data = []
                # 再次循环 new_rows 来添加 student_name 和 answer_score
                for new_row in new_rows:
                    student_id = new_row[2]
                    student_name = student_dic.get(student_id, "Null")
                    answer_id = new_row[3]
                    answer_no, answer_qr_code = small_question_dic.get(answer_id, ("Null", "Null"))
                    student_row = new_row + [student_name, answer_no, answer_qr_code]

                    if student_row[5] == student_row[4]:
                        get_score = student_row[6]
                        evaluate_results = "1"
                    else:
                        get_score = "0"
                        evaluate_results = "0"

                    finally_row = student_row + [evaluate_results, get_score]

                    # 提取第一位和第二位
                    question_type = finally_row[11][0]  # 第一位代表题目类型
                    section_number = finally_row[11][1]  # 第二位代表大题号
                    if question_type == "1":
                        question_type = "单选题"
                    elif question_type == "2":
                        question_type = "多选题"
                    elif question_type == "3":
                        question_type = "判断题"

                    # 转换第二位为中文大写数字
                    converted_section_number = self.convert_section_number(section_number)

                    # 组合新的值
                    new_value = f"{converted_section_number}、{question_type}"
                    finally_row[11] = new_value  # 更新 new_row[3] 的值

                    # 创建一个字典来存储字段名和对应的值
                    row_dict = dict(zip(field_names, finally_row))
                    self.detail_data.append(row_dict)

                self.results_data = []
                for index, entry in enumerate(self.detail_data, start=1):
                    row = [str((self.page_number - 1) * self.items_per_page + index)]  # 序号
                    # 使用 self.cn2en1 提取相应的值
                    for key in list(self.cn2en1.keys())[1:]:  # 从第二个键开始，因为第一个是序号
                        row.append(str(entry.get(str(self.cn2en1[key]), " ")))  # 以字符串显示
                    self.results_data.append(row)
            total_count = len(self.now_data)
            total_pages = (total_count + items_per_page - 1) // items_per_page
            self.data2_signal.emit(self.results_data, total_pages, total_count)

        elif len(keyword) == 0 and self.mainwindows.pushButton_25.text() == "返回":
            self.mainwindows.ckxf()

        print(self.results_data)

    def convert_section_number(self, section_number):
        """将数字转换为中文大写数字"""
        chinese_numbers = {
            '1': '一', '2': '二', '3': '三', '4': '四',
            '5': '五', '6': '六', '7': '七', '8': '八',
            '9': '九', '10': '十'
        }
        return chinese_numbers.get(section_number, '')

    def calculate_scores(self, answers):
        # 初始化一个字典来存储每个大题的得分情况
        score_dict = {}

        # 遍历答题数据
        for subject_code, exam_number, candidate_number, question_group, question_number, correct_answer, student_answer, score in answers:
            # 确保 score 是整数
            if score is not None:
                score = int(score)

            # 创建一个唯一的键来标识每个大题
            key = (subject_code, exam_number, question_number, candidate_number)

            # 如果大题不在字典中，初始化它的得分
            if key not in score_dict:
                score_dict[key] = {
                    'paper_id': exam_number,
                    'subject_code': subject_code,
                    'answer_id': question_number,
                    'student_id': candidate_number,
                    'total_score': 0
                }

            # 如果学生答案正确，则累加分数
            if student_answer == correct_answer and isinstance(score, int):
                score_dict[key]['total_score'] += score

        # 将字典的值转换为列表
        return list(score_dict.values())

    def convert_chinese_to_arabic(self, chinese_number):
        # 将中文数字转换为阿拉伯数字
        chinese_to_arabic = {
            '一': '1', '二': '2', '三': '3', '四': '4',
            '五': '5', '六': '6', '七': '7', '八': '8',
            '九': '9', '十': '10'
        }
        return chinese_to_arabic.get(chinese_number, '0')